package com.situ.crm.dao.impl;

import com.situ.crm.dao.IUserDao;
import com.situ.crm.pojo.User;
import com.situ.crm.pojo.UserRoleCount;
import com.situ.crm.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//Alt+Enter
public class UserDaoImpl implements IUserDao {
    @Override
    public List<User> selectAll(String searchName) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<User> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnection();
            String sql = "select id,name,password,image,email,phone,role,status,create_time,update_time from user where name like ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, "%" + searchName + "%");
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String password = resultSet.getString("password");
                String email = resultSet.getString("email");
                int role = resultSet.getInt("role");
                int status = resultSet.getInt("status");
                String image = resultSet.getString("image");
                //没遍历一次就查询出user表中的一条数据，封装成一个User对象
                User user = new User();
                user.setId(id);
                user.setName(name);
                user.setPassword(password);
                user.setEmail(email);
                user.setRole(role);
                user.setStatus(status);
                user.setImage(image);
                list.add(user);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return list;
    }

    @Override
    public void deleteById(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "delete from user where id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            System.out.println(statement);
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void add(User user) {
        //链接
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "insert into user(name,password,email,role,image) values(?,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1, user.getName());
            statement.setString(2, user.getPassword());
            statement.setString(3, user.getEmail());
            statement.setInt(4, user.getRole());
            statement.setString(5, user.getImage());
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public User selectById(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "select id,name,password,image,email,phone,role,status,create_time,update_time from user where id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String password = resultSet.getString("password");
                String email = resultSet.getString("email");
                int role = resultSet.getInt("role");
                int status = resultSet.getInt("status");
                String image = resultSet.getString("image");
                //没遍历一次就查询出user表中的一条数据，封装成一个User对象
                user = new User();
                user.setId(id);
                user.setName(name);
                user.setPassword(password);
                user.setEmail(email);
                user.setRole(role);
                user.setStatus(status);
                user.setImage(image);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return user;
    }

    @Override
    public void update(User user) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "update user set name=?,password=?,email=?,role=?,image=?,status=? where id=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, user.getName());
            statement.setString(2, user.getPassword());
            statement.setString(3, user.getEmail());
            statement.setInt(4, user.getRole());
            statement.setString(5, user.getImage());
            statement.setInt(6, user.getStatus());
            statement.setInt(7, user.getId());
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public User login(String name, String password) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "select id,name,password,image,email,phone,role,status,create_time,update_time from user where name=? and password=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            statement.setString(2, password);
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String email = resultSet.getString("email");
                int role = resultSet.getInt("role");
                int status = resultSet.getInt("status");
                String image = resultSet.getString("image");
                //没遍历一次就查询出user表中的一条数据，封装成一个User对象
                user = new User();
                user.setId(id);
                user.setName(name);
                user.setPassword(password);
                user.setEmail(email);
                user.setRole(role);
                user.setStatus(status);
                user.setImage(image);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return user;
    }

    @Override
    public List<UserRoleCount> selectRoleCount() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<UserRoleCount> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT CASE WHEN role=1 THEN '超级管理员' WHEN role=2 THEN '普通管理员' END AS 'name',COUNT(*) AS 'value'\n" +
                    "FROM `user` GROUP BY role";
            statement = connection.prepareStatement(sql);
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                int value = resultSet.getInt("value");
                UserRoleCount userRoleCount = new UserRoleCount();
                userRoleCount.setName(name);
                userRoleCount.setValue(value);
                list.add(userRoleCount);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return list;
    }
}
